package com.fly.demo.template;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.annotation.Transactional;

import com.fly.demo.template.bean.StudentVO;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Transactional
@SpringBootTest(webEnvironment = WebEnvironment.NONE)
public class JdbcTemplateTest2
{
    @Autowired
    JdbcTemplate jdbcTemplate;
    
    @BeforeEach
    public void init()
    {
        // execute可以执行所有SQL语句，因为没有返回值，一般用于执行DDL语句
        jdbcTemplate.execute("drop table if exists student");
        jdbcTemplate.execute("create table student(id bigint NOT NULL AUTO_INCREMENT, stu_name varchar(50), primary key(id))");
        jdbcTemplate.execute("insert into student(stu_name) values('Jack')");
        jdbcTemplate.execute("insert into student(stu_name) values('Phil')");
        jdbcTemplate.execute("insert into student(stu_name) values('Jenny')");
        
        // batchUpdate
        jdbcTemplate.batchUpdate("insert into student(stu_name) values(?)", Arrays.asList(new Object[][] {{"Tom"}, {"Jerry"}}));
        log.info("::: init success!!");
        log.info(">>>>> before: {}", jdbcTemplate.queryForObject("select count(*) from student", Long.class));
    }
    
    @AfterEach
    public void after()
    {
        log.info(">>>>> after : {}", jdbcTemplate.queryForObject("select count(*) from student", Long.class));
    }
    
    /**
     * 查询主键
     */
    @Test
    public void testQueryPk()
    {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator()
        {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn)
                throws SQLException
            {
                // 预处理,注意参数PreparedStatement.RETURN_GENERATED_KEYS
                PreparedStatement ps = conn.prepareStatement("insert into student(stu_name) values(?)", PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setString(1, "Chery");
                return ps;
            }
        }, keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
        
        // lambda写法
        jdbcTemplate.update((conn) -> {
            PreparedStatement ps = conn.prepareStatement("insert into student(stu_name) values(?)", PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, "Chery");
            return ps;
        }, keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
    }
    
    /**
     * 查询非自增主键
     */
    @Test
    public void testQueryPk2()
    {
        jdbcTemplate.execute("drop table if exists student2");
        jdbcTemplate.execute("create table student2(id bigint NOT NULL, stu_name varchar(50), primary key(id))");
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update((conn) -> {
            PreparedStatement ps = conn.prepareStatement("insert into student2(id, stu_name) values(?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setInt(1, 11);
            ps.setString(2, "Chery");
            return ps;
        }, keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
    }
    
    @Test
    public void testQueryBean()
    {
        RowMapper<StudentVO> rowMapper = new BeanPropertyRowMapper<>(StudentVO.class);
        StudentVO studentVO = jdbcTemplate.queryForObject("select id, stu_name from student where id=?", rowMapper, 1);
        log.info("{}", studentVO);
        
        List<StudentVO> list = jdbcTemplate.query("select id, stu_name from student", rowMapper);
        log.info("{}", list);
        
        // bean对象
        log.info("{}", jdbcTemplate.query("select * from student where stu_name like ?", rowMapper, "%小%"));
        log.info("{}", jdbcTemplate.query("select * from student where stu_name like concat('%', ?, '%')", rowMapper, "小"));
    }
    
    @Test
    public void testBatchUpdate()
    {
        String sql = "insert into student(stu_name) values(?)";
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        batchArgs.add(new Object[] {"小马"});
        batchArgs.add(new Object[] {"大马"});
        batchArgs.add(new Object[] {"小刘"});
        batchArgs.add(new Object[] {"大刘"});
        batchArgs.add(new Object[] {"小强"});
        batchArgs.add(new Object[] {"大强"});
        jdbcTemplate.batchUpdate(sql, batchArgs);
    }
}
